package com.example.demo.dao;

import com.example.demo.Enity.Activity;
import com.example.demo.Enity.indexAll;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;

import javax.transaction.Transactional;
import java.util.ArrayList;

public interface ActivityJpa extends JpaRepository<Activity, Integer>, JpaSpecificationExecutor<Activity> {
    //select U.image,U.name,U.oppenid,U.university,U.money,U.reallname from travel_relate R join travel_user U on R.otherid=U.oppenid where R.mineid=?1"
    @Query(nativeQuery = true, value = "select A.id,A.address,A.category,A.ided,A.img,A.master,A.time,A.wx,U.yb_userhead,A.information from activity A join user U on A.ided=U.yb_userid where category=?1")
    ArrayList getCategoey(String category);

    @Query(nativeQuery = true, value = "select A.id,A.address,A.category,A.ided,A.img,A.master,A.time,A.wx,U.yb_userhead,A.information from activity A join user U on A.ided=U.yb_userid where category=?1 and A.information like %?2%")
    ArrayList getCategoey2(String category,String findContent);

    @Query(nativeQuery = true, value = "select A.id,A.address,A.category,A.ided,A.img,A.master,A.time,A.wx,U.yb_userhead,A.information from activity A join user U on A.ided=U.yb_userid where category=?1 and A.id = ?2")
    ArrayList getCategoey3(String category,String id);

    @Modifying
    @Transactional
    @Query(nativeQuery = true, value = "update activity set master=?2 where ided=?1")
    void updateName(String username,String nickname);

    @Query(nativeQuery = true, value = "SELECT I.category,I.fen,I.id,I.img,COUNT(*) AS num2 FROM activity A JOIN index_all I ON A.category=I.category WHERE fen=?1 GROUP BY A.category ORDER BY I.id")
    ArrayList getIndexall(String fen);

}
